﻿using System.Collections.Generic;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using WF_DataAccess;
using System;
using ExtBusiness.FlowOperation;
using ExtBusiness.CommonBusiness;
using System.Configuration;
using System.Data.OracleClient;
using WF_Business;
using ChBusiness;
using ChBusiness.CommonBusiness;

namespace ExtBusiness.FlowOperation
{
    /// <summary>
    /// 工作流创建、流转操作类
    /// </summary>
    public class RequestFlowOperation
    {
        /// <summary>
        /// 实例化对象
        /// </summary>
        public static RequestFlowOperation Instance { get { return new RequestFlowOperation(); } }
        Database dbGloab = BasicOperate.CreateNetOfficeDataBase();

        /// <summary>
        /// 获取请求的流向
        /// </summary>
        /// <param name="strFlowId"></param>
        /// <returns></returns>
        public DataTable GetRequestFlow(string strFlowId)
        {
            string strSql = "select * from xt_request_step t where 1=1";
            if (!string.IsNullOrEmpty(strFlowId))
            {
                strSql += string.Format(" and flowid='{0}'", strFlowId);
            }
            strSql += " order by step_no";

            DataTable dtTemp = dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];

            return dtTemp;
        }

        /// <summary>
        /// 获取用户岗位编号
        /// </summary>
        /// <param name="strUserId"></param>
        /// <returns></returns>
        public string GetStepNoByUser(string strUserId)
        {
            string strSql = string.Format("select a.STEPNO from SYS_user a where USERID='{0}'", strUserId);

            object objReturn = dbGloab.ExecuteScalar(CommandType.Text, strSql);
            if (objReturn != null)
            {
                return objReturn.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 根据用户id获取所能受理的工作流
        /// </summary>
        /// <param name="strUserId">用户id</param>
        /// <returns></returns>
        public DataTable GetWorkFlowByUserId(string strUserId)
        {
            string strSql = string.Format(@"select m.* from st_workflow m, st_step n  Where  m.WID in (select WID from st_group_in_step where GID in (select GID from st_user_group t 
 where t.userid = '{0}') And step_type=0 ) and m.WID = n.Wid and n.step_type = 0 and m.rot = 0 order by m.wname desc",
            strUserId);

            DataTable dtReturn = dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];

            return dtReturn;
        }

        /// <summary>
        /// 更新地图用途
        /// </summary>
        /// <param name="strIID">办件编号</param>
        /// <param name="strDtyt">地图用途</param>
        public void UpdateDtyt(string strIID, string strDtyt)
        {
            string strSql = string.Format(@"update xt_instance_ext a set a.dtyt='{0}' 
          where iid = '{1}'", strDtyt, strIID);
            dbGloab.ExecuteNonQuery(CommandType.Text, strSql);
        }

        /// <summary>
        /// 根据办件编号获取申报信息并拷贝添加
        /// </summary>
        /// <param name="strOldIid"></param>
        /// <param name="strUserId"></param>
        /// <returns></returns>
        public string CopyAndAdd(string strOldIid, string strUserId)
        {
            string strWiid = string.Empty;
            string strErrorMsg = string.Empty;
            string strReturn = string.Empty;

            string strSql = string.Format(@"select wid,name,
       (select a.dtyt
          from xt_instance_ext a
         where a.iid = t.iid
           and rownum < 2) dtyt
  from st_instance t
 where iid = '{0}'", strOldIid);
            DataTable dtReturn = dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];

            string strIid = "", strWid = "", strRequestName = "", strDtyt = "";
            if (dtReturn.Rows.Count > 0)
            {
                strWid = dtReturn.Rows[0]["wid"].ToString();
                strRequestName = dtReturn.Rows[0]["name"].ToString();
                strDtyt = dtReturn.Rows[0]["dtyt"].ToString();
            }

            if (!string.IsNullOrEmpty(strWid))
            {
                WF_Business.WorkFlow work = new WF_Business.WorkFlow();
                IDataAccess dataAccess = SysParams.OAConnection(true);

                try
                {
                    if (true == work.NewInstance(strWid, strUserId, strRequestName, ref strIid, ref strWiid, ref strErrorMsg, ref dataAccess))
                    {
                        //插入工作流实例的扩展信息
                        strSql = string.Format("insert into xt_instance_ext(iid,申请单位,DTYT) values('{0}','{1}','{2}')", strIid, strRequestName, strDtyt);
                        dataAccess.RunSql(strSql);

                        ClsWorkFlow cuwfTemp = new ClsWorkFlow();
                        string strWname = cuwfTemp.GetFlowName(strWid);
                        if (strWname == "地图审核(即送即审)")
                        {
                            strSql = string.Format(@"update st_instance set PRIORITY='2' where iid='{0}'", strIid);
                            dataAccess.RunSql(strSql);
                        }

                        OracleSystemOperation osoTemp = new OracleSystemOperation();
                        osoTemp.GetNewTaskSql(strIid, strOldIid, "UT_国家测绘局地图审核申请表", dataAccess);

                        dataAccess.Close(true);
                        strReturn = "../" + ConfigurationManager.AppSettings["dbsxUrl"];
                    }
                }
                catch
                {
                    dataAccess.Close(false);
                }
            }

            return strReturn;
        }


        /// <summary>
        /// 创建流程实例
        /// </summary>
        /// <param name="strWid">办件id</param>
        /// <param name="strUserId">用户id</param>
        /// <param name="strRequestName">申请人</param>
        /// <param name="strDtyt">地图用途</param>
        /// <param name="strParamsWiid">寄存岗位编号</param>
        /// <param name="strErrorMsg">寄存错误消息</param>
        /// <returns></returns>
        public int BuildTask(string strWid, string strUserId, string strRequestName, string strDtyt, ref string strParamsWiid, ref string strErrorMsg)
        {
            WF_Business.WorkFlow work = new WF_Business.WorkFlow();
            string strIid = "", strWiid = "";
            int nReturn = 0;
            IDataAccess dataAccess = SysParams.OAConnection(true);

            try
            {
                if (true == work.NewInstance(strWid, strUserId, strRequestName, ref strIid, ref strWiid, ref strErrorMsg, ref dataAccess))
                {
                    //插入工作流实例的扩展信息
                    string strSql = string.Format("insert into xt_instance_ext(iid,申请单位,DTYT) values('{0}','{1}','{2}')", strIid, strRequestName, strDtyt);
                    dataAccess.RunSql(strSql);

                    ClsWorkFlow cuwfTemp = new ClsWorkFlow();
                    string strWname = cuwfTemp.GetFlowName(strWid);
                    if (strWname == "地图审核(即送即审)")
                    {
                        strSql = string.Format(@"update st_instance set PRIORITY='2' where iid='{0}'", strIid);
                        dataAccess.RunSql(strSql);
                    }
                    else if (strWname == "国家涉密基础测绘成果使用申报")
                    {
                        strSql = string.Format(@"insert into  UT_测绘成果使用申请表 t(iid,t.单位名称,邮政编码1,t.详细地址1,t.申请人代码,t.经办人姓名,t.身份证号码,t.联系电话1,t.电子邮箱1,
t.机构名称,t.详细地址2,t.邮政编码2,t.人员姓名,t.联系电话2,t.电子邮箱2,t.机关名称,t.详细地址3,t.邮政编码3,t.承办人姓名,t.联系电话3)
select '{0}',a.unitsname,
       a.postcode,
       a.unitsaddress,
       a.organization,
       (select b.jbrname
          from ut_jbrinfo b
         where b.unitsid = a.id
           and rownum < 2) jbrname,
       (select b.jbrcard
          from ut_jbrinfo b
         where b.unitsid = a.id
           and rownum < 2) jbrcard,
       (select b.jbrtel
          from ut_jbrinfo b
         where b.unitsid = a.id
           and rownum < 2) jbrtel,
           (select b.jbremail
          from ut_jbrinfo b
         where b.unitsid = a.id
           and rownum < 2) jbremail,
           bmxx.orgname,bmxx.orgaddress,bmxx.orgpostcode,bmxx.jbrname,bmxx.jbrtel,bmxx.orgemail,
        zmxx.orgname,zmxx.orgaddress,zmxx.orgpostcode,zmxx.jbrname,zmxx.jbrtel
  from sys_units a, (select *
          from ut_unitsrelation m where m.orgtype=1) bmxx,(select *
          from ut_unitsrelation m where m.orgtype=2) zmxx
 where a.unitsname = '{1}' and a.id=bmxx.unitsid(+) and a.id=zmxx.unitsid(+)", strIid,strRequestName);
                        dataAccess.RunSql(strSql);
                    }

                    strParamsWiid = strWiid;
                    dataAccess.Close(true);
                    nReturn = 1;
                }
            }
            catch (Exception ex)
            {
                if (string.IsNullOrEmpty(strErrorMsg))
                {
                    strErrorMsg = ex.Message;
                }

                nReturn = -1;
                dataAccess.Close(false);
            }
            return nReturn;
        }

        /// <summary>
        /// 技术审查中心统计
        /// </summary>
        /// <param name="strUserId"></param>
        /// <param name="strOtherWhere"></param>
        /// <returns></returns>
        public DataTable GetJszxDataCollect(string strUserId, string strOtherWhere)
        {
            string strSql = string.Format(@"select count(distinct i.iid) bjs,sum(regexp_replace(t.图幅数量,'[^0-9]')）tfzs,c.dtyt
  from st_instance                 i, 
       st_workflow                 w,
       xt_instance_ext             c,
       ut_国家测绘局地图审核申请表 t
 where i.wid = w.wid
   and i.iid = c.iid
   and i.iid = t.iid(+)
   and not exists (select flowname
          from xt_workflow_define b
         where b.ispub = '0'
           and b.flowname = w.wname)");

            if (!string.IsNullOrEmpty(strUserId))//受理人员
                strSql += string.Format(@" and exists (select t.iid
          from st_work_item_hist t where t.iid=i.iid 
         and t.userid = '{0}')", strUserId);

            if (!string.IsNullOrEmpty(strOtherWhere))//其他条件
                strSql += " " + strOtherWhere;

            strSql += "  group by c.dtyt";

            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 根据案件编号获取订单id
        /// </summary>
        /// <param name="strIID"></param>
        ///  <param name="strType"></param>
        /// <returns></returns>
        public DataTable GetSmcgAppInfo(string strIID,string strType)
        {
            string strSql = string.Format(@"select rownum rn,a.* from xt_webMapOrderInfo a,xt_appid_iid b where a.appid=b.appid and b.iid='{0}' and a.apptype ='{1}' order by apptype,a.newmapno", strIID, strType);

            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }
        /// <summary>
        /// 根据案件iid获取订单概要信息
        /// </summary>
        /// <param name="strIID"></param>
        /// <returns></returns>
        public DataTable GetSmcgGYXX(string strIID)
        {
            string strSql = string.Format(@"select ddgyxx from ut_smcg_ddgy  where  iid='{0}'  ", strIID);
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 根据案件编号获取订单id
        /// </summary>
        /// <param name="strIID"></param>
        /// <returns></returns>
        public DataTable GetSmcgDdgyInfo(string strIID)
        {
            string strSql = string.Format(@"select b.appid,a.* from UT_SMCG_DDGY a,xt_appid_iid b where a.iid=b.iid and a.iid='{0}'", strIID);

            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 获取涉密成果id
        /// </summary>
        /// <param name="strIID"></param>
        /// <param name="strUserId"></param>
        /// <param name="strAppid"></param>
        /// <returns></returns>
        public void GetSmcgAppId(string strIID,ref string strUserId,ref string strAppid)
        {
            string strSql = string.Format(@"select appid,(select t.requester from xt_submit_info t where t.iid=b.iid) userid from xt_appid_iid b where b.iid='{0}'", strIID);
            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            if (dtReturn.Rows.Count > 0)
            {
                strUserId = dtReturn.Rows[0]["userid"].ToString();
                strAppid = dtReturn.Rows[0]["appid"].ToString();
            }
        }

        /// <summary>
        /// 显示待办案件列表
        /// </summary>
        /// <param name="strUserId"></param>
        /// <param name="searchWord"></param>
        /// <returns></returns>
        public DataTable AppearWorkItem(string strUserId, string searchWord)
        {
            string strSql = string.Format(@"select 顺序号,
               案件类型,
               申请单位,
               接收时间,
               前一用户,
               剩余工作日,
               所在岗位,
               优先级,
               案件状态,
               userid,
               督办过期,
               结点过期,
               wiid,a.stepctlid,a.wiid,
               isback,
               extotaltime,
               联系人
          from sv_work_list_for_worklist_node a
         where (userid = '{0}' or
               userid in (select userid
                             from st_delegate
                            Where delegate_userid = '{0}'
                              And (delegate_task = a.案件类型 or delegate_task = '全部')
                              and begin_time <= Sysdate
                              And end_time >= Sysdate))
           and 顺序号 not in (select iid from st_suspend where end_date is null) {1} 
         order by 接收时间 desc",
               strUserId, searchWord);

            DataTable dtReturn = new DataTable();
            dtReturn = dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];

            return dtReturn;
        }

        /// <summary>
        /// 显示当前岗位的待办事项
        /// </summary>
        /// <param name="strUserId">登录用户id</param>
        /// <param name="searchWord">扩展查询条件</param>
        /// <returns></returns>
        public DataTable AppearTaskInStep(string strUserId, string searchWord, int nType)
        {
            string strUserWhere = string.Empty;
            if (!string.IsNullOrEmpty(strUserId))
            {
                strUserWhere += string.Format(@" and (userid = '{0}' or
      userid in (select userid
                    from st_delegate
                   Where delegate_userid = '{0}'
                     And (delegate_task = c.案件类型 or delegate_task = '全部')
                     and begin_time <= Sysdate
                     And end_time >= Sysdate))", strUserId); ;
            }

            string strSql = string.Empty;

            if (nType == 0)//针对受理大厅和技术审查中心
            {
                strSql = string.Format(@"select c.顺序号,wiid,c.stepctlid,n.slbh,
(select 审查意见提交日期 from UT_地图技术审查通知书 k where k.iid=c.顺序号 and k.input_index=0) 审查应完成日期,
       a.单位名称,a.图名,a.图幅数量,a.规格,a.所有用途,a.收件日期,
(select dtyt from xt_instance_ext t where t.iid = c.顺序号 and rownum<2) dtyt,
(select user_name from st_user p where p.userid=c.userid and rownum<2)  blr,
       a.受理日期,a.送交审查机构日期,c.所在岗位,
(select decode(status, 2, '通过', 4, '协审通过', -4, '不通过') from xt_instance_ext t
                  where t.iid = c.顺序号) sftg,
(select is_css from xt_instance_ext b where b.iid = c.顺序号) is_css,
       c.联系人,案件类型 ,优先级,剩余工作日,isback,前一用户,c.督办过期,c.结点过期   
  from ut_国家测绘局地图审核申请表 a,
       sv_work_list_for_worklist_node c,ut_地图审核受理通知书 n
 where c.顺序号 = a.iid(+) and  c.顺序号 = n.iid(+) {0}        
   and not exists(select iid from st_suspend where end_date is null and iid=顺序号) {1} 
 order by slbh desc", strUserWhere, searchWord);
            }
            else if (nType == 1)//针对地图管理处
            {
                strSql = string.Format(@"select c.顺序号,wiid,c.stepctlid,n.slbh,
(select 审查意见提交日期 from UT_地图技术审查通知书 k where k.iid=c.顺序号 and k.input_index=0) 审查应完成日期,
       a.单位名称,a.图名,a.所有用途,
(select dtyt from xt_instance_ext t where t.iid = c.顺序号 and rownum<2) dtyt,
(select user_name from st_user p where p.userid=c.userid and rownum<2)  blr,
       a.受理日期,a.送交审查机构日期,c.所在岗位,
(select decode(status, 2, '通过', 4, '协审通过', -4, '不通过') from xt_instance_ext t
                  where t.iid = c.顺序号) sftg,
(select is_css from xt_instance_ext b where b.iid = c.顺序号) is_css,
       replace(案件类型,'地图审核','') 案件类型,优先级,
(select trunc((c.extotaltime-compute_date.compute_work_time(c.accepted_time,sysdate))/compute_date.get_worktime_in_oneday(sysdate),0) from st_instance c where c.iid = c.顺序号) 剩余工作日,
isback,前一用户    
  from ut_国家测绘局地图审核申请表 a,
       sv_work_list_for_worklist_node c,ut_地图审核受理通知书 n
 where c.顺序号 = a.iid(+) and  c.顺序号 = n.iid(+) {0}        
   and not exists(select iid from st_suspend where end_date is null and iid=顺序号) {1} 
 order by slbh", strUserWhere, searchWord);
            }

            DataTable dtReturn = new DataTable();
            dtReturn = dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];

            return dtReturn;
        }


        /// <summary>
        /// 地图内容检定专项查询
        /// </summary>
        /// <param name="strUserId">用户id</param>
        /// <param name="strWhere">查询条件</param>
        /// <returns></returns>
        public DataTable QueryWenTiMapData(string strUserId, string strWhere)
        {
            string strSql = string.Format(@"select i.iid,i.name,t.图名,t.图幅数量,t.规格,
       (select jdbh from UT_地图内容检定意见书 where iid = i.iid) jdbh,
       a.来源,t.所有用途,
       a.举报人,
       decode(i.status,1,'在办',2,'办结') spzt,
       a.举报时间
  from st_instance                 i,
       UT_地图内容检定记录表       a,
       ut_国家测绘局地图审核申请表 t
 where i.iid = a.iid
   and i.iid = t.iid
   and exists (select wid
          from st_workflow m
         where m.wid = i.wid
           and m.wname like '%检定%')");

            if (!string.IsNullOrEmpty(strUserId))//受理人员
                strSql += string.Format(@" and exists (select t.iid
          from st_work_item_hist t where t.iid=i.iid 
         and t.userid = '{0}')", strUserId);
            if (!string.IsNullOrEmpty(strWhere))//受理编号
                strSql += string.Format(" {0}", strWhere);

            DataTable dtReturn = new DataTable();
            dtReturn = dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];

            return dtReturn;
        }


        /// <summary>
        /// 按用户ID获取我已批核的件
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <param name="strOtherWhere">其他条件</param>
        /// <returns></returns>
        public DataTable GetMyHandledByUserID(string strUserID, string strOtherWhere)
        {
            string strSql = string.Format(@"select m.顺序号 iid,
       m.接收时间 accepted_time,finish_time,(select 司领导签发日期
                    from UT_地图审核意见表 m
                   where m.iid = d.iid) qfrq,
       m.所在岗位 step,
       m.申请单位 name,
       (select  decode(t.submitflag,2,d.批准号,-4,d.不予批准号,4,d.协审批准号) 
          from xt_submit_info t
         where t.iid = m.顺序号 and rownum<2) scbh,
       m.案件类型 wname,
       d.单位名称 sqdw,
       d.图名 tm,d.规格,m.dtyt,
       slbh slh,
       m.案件状态 status
  from sv_work_list_for_worklist_node m,ut_地图审核受理通知书 a,ut_国家测绘局地图审核申请表 d
 where m.顺序号=a.iid(+) and m.顺序号=d.iid(+) and not exists 
       (select flowname from xt_workflow_define b where b.ispub = '0' and b.flowname=m.案件类型)");

            if (!string.IsNullOrEmpty(strUserID))//受理人员
                strSql += string.Format(@" and exists (select t.iid
          from st_work_item_hist t where t.iid=m.顺序号 
         and t.userid = '{0}')", strUserID);

            if (!string.IsNullOrEmpty(strOtherWhere))//其他条件
                strSql += " " + strOtherWhere;

            strSql += " order by m.顺序号 desc";

            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 针对受理窗口和技术审查中心，按用户ID获取我已批核但尚未办理完成的件
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <param name="strWhere">其他条件</param>
        /// <returns></returns>
        public DataTable GetMyShenPiByUserID(string strUserID, string strWhere)
        {
            string strSql = string.Format(@"select distinct m.顺序号 iid,
                m.接收时间 accepted_time,
                m.所在岗位 step,
                t.单位名称 name,
                (case
                  when c.iid is null then
                   '大厅'
                  else
                   '网上'
                end) sjly,
                m.案件类型 wname,
                nvl(x.is_tg, 0) is_tg,
                nvl(x.is_gd, 0) is_gd,
                (select 审查意见提交日期
                   from UT_地图技术审查通知书 k
                  where k.iid = m.顺序号
                    and k.input_index = 0) 审查应完成日期,
                t.图名 tm, t.收件日期, t.受理日期, t.送交审查机构日期,
                slbh slh,t.图幅数量,x.dtyt,
                m.案件状态 status
  from sv_work_list_for_worklist_node m,
       xt_instance_ext                x,
       xt_submit_info                 c,
       ut_地图审核受理通知书          a,
       ut_国家测绘局地图审核申请表 t
 where m.顺序号 = c.iid(+)
   AND m.顺序号 = a.iid(+) AND m.顺序号 = t.iid(+)
   and m.顺序号 = x.iid
   and not exists (select flowname
          from xt_workflow_define b
         where b.ispub = '0'
           and b.flowname = m.案件类型)");

            if (!string.IsNullOrEmpty(strUserID))//受理人员
                strSql += string.Format(@" and exists (select t.iid
          from st_work_item_hist t where t.iid=m.顺序号 
         and t.userid = '{0}')", strUserID);
            if (!string.IsNullOrEmpty(strWhere))//图件类别
                strSql += string.Format(" {0}", strWhere);

            strSql += " order by a.slbh desc";

            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 针对受理窗口和技术审查中心，按用户ID获取我已批核并办完的件
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <param name="strWhere">查询条件</param>
        /// <returns></returns>
        public DataTable GetMyShenPiResultByUserID(string strUserID, string strWhere)
        {
            string strSql = string.Format(@"select distinct i.iid,
       i.accepted_time,
       i.name,
       w.wname,
       t.图名 tm,decode(c.status,2,t.批准号,-4,t.不予批准号,4,t.协审批准号) scbh,
 (select 审查意见提交日期
                   from UT_地图技术审查通知书 k
                  where k.iid = i.iid 
                    and k.input_index = 0) 审查应完成日期,
                t.收件日期, t.受理日期, t.送交审查机构日期,
       slbh slh,i.priority,c.dtyt,t.BAQK,t.CCQK,SJBARQ,CCSJ,t.图幅数量,
       i.status
  from st_instance           i,
       st_workflow           w,
xt_instance_ext           c,
       ut_地图审核受理通知书 a,
ut_国家测绘局地图审核申请表 t
 where i.wid = w.wid
 and i.iid=c.iid 
   AND i.iid = a.iid(+) and i.iid=t.iid(+)
   AND i.status='2'
   and not exists (select flowname from xt_workflow_define b where b.ispub = '0' and b.flowname=w.wname)");

            if (!string.IsNullOrEmpty(strUserID))//受理人员
                strSql += string.Format(@" and exists (select t.iid
          from st_work_item_hist t where t.iid=i.iid 
         and t.userid = '{0}')", strUserID);
            if (!string.IsNullOrEmpty(strWhere))//受理编号
                strSql += string.Format(" {0}", strWhere);

            strSql += " order by i.iid desc";

            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 针对技术审查中心，获取需要备案的件
        /// </summary>
        /// <param name="strWhere">查询条件</param>
        /// <returns></returns>
        public DataTable GetNeedBakResult(string strWhere)
        {
            string strSql = @"select distinct i.iid,
       i.accepted_time,
       i.name,
       w.wname,
       t.图名 tm,t.批准号,
       slbh slh,i.priority,c.dtyt, getSyqx(round(decode(t.JGRQ,null,add_months(i.finish_time,2),t.JGRQ)-sysdate),t.baqk) syqx,t.BAQK,t.CCQK,SJBARQ,CCSJ,
       i.status
  from st_instance           i,
       st_workflow           w,
xt_instance_ext           c,
       ut_地图审核受理通知书 a,
ut_国家测绘局地图审核申请表 t
 where i.wid = w.wid
 and i.iid=c.iid 
   AND i.iid = a.iid and i.iid=t.iid
   AND i.status='2'
   and not exists (select flowname from xt_workflow_define b where b.ispub = '0' and b.flowname=w.wname)";

            if (!string.IsNullOrEmpty(strWhere))//受理编号
                strSql += string.Format(" {0}", strWhere);

            strSql += " order by i.iid desc";

            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 按业务状态获取案件信息
        /// </summary>
        /// <param name="strStatus">业务状态</param>
        /// <param name="strOtherWhere">其他条件</param>
        /// <returns></returns>
        public DataTable GetMyHandledByStatus(string strStatus, string strOtherWhere)
        {
            string strSql = @"select a.iid,
                                               b.accepted_time,b.finish_time,(select 司领导签发日期
                    from UT_地图审核意见表 m
                   where m.iid = d.iid) qfrq,
                                               b.name,b.priority,
                                               c.wname,a.status,
                                               decode(a.status, 2, d.批准号, -4, d.不予批准号, 4, d.协审批准号) scbh,
       d.单位名称 sqdw,
       d.图名 tm,d.规格,a.dtyt,
      (select t.slbh
          from ut_地图审核受理通知书 t
         where t.iid = a.iid
           and input_index = 0
           and rownum < 2) slh,
       '' submit_time,
       '' step  from xt_instance_ext a, st_instance b, st_workflow c,ut_国家测绘局地图审核申请表 d
                                         where a.iid = b.iid
                                           and b.wid = c.wid  and a.iid = d.iid(+)
and not exists (select flowname from xt_workflow_define m where m.ispub = '0' and m.flowname=c.wname)";

            string strSpecialSerial = GetSpecialSerial(strStatus);
            string strOrderBy = " order by a.iid";
            if (strStatus == "ok")//已受理的件 包括:已中止、通过、未通过
                strSql += string.Format(" and b.status=2 and (a.status = '2' or a.status = '4' or a.status = '-4') {0}", strSpecialSerial);
            else if (!string.IsNullOrEmpty(strStatus))//审批状态
            {
                strSql += string.Format(" and (a.status = '{0}' {1}) {2}", strStatus, GetWhereByStatus(strStatus), strSpecialSerial);

                if (strStatus == "2" || strStatus == "4" || strStatus == "-4")
                {
                    strSql += " and b.status=2 ";//必须设置为结束

                    strOrderBy = "order by d.sort_num desc";
                }
            }

            if (!string.IsNullOrEmpty(strOtherWhere))//其他条件
                strSql += " " + strOtherWhere;

            strSql += strOrderBy;

            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 从已办件中执行回退
        /// </summary>
        /// <param name="strIID">办件编号</param>
        /// <returns></returns>
        public void ReturnTaskFromComplete(string strIID)
        {
            OracleParameter param1 = new OracleParameter();
            param1.ParameterName = "diid";
            param1.DbType = System.Data.DbType.Decimal;
            param1.Value = strIID;

            System.Data.IDataParameter[] paramArray = new OracleParameter[1];
            paramArray[0] = param1;
            SysParams.OAConnection().RunProc("Return_complete_task", ref paramArray);
        }

        /// <summary>
        /// 获取特殊的办件类型
        /// </summary>
        /// <param name="strStatus">状态位信息</param>
        /// <returns></returns>
        private string GetSpecialSerial(string strStatus)
        {
            //0:已受理;2:已办结;3:已备案;4:协审通过；-1:返回补证;-2:驳回中止;-3:不予受理;-4:审批不通过
            string strWhere = string.Empty;
            switch (strStatus)
            {
                case "ok"://所有已办结
                case "0"://正在办理
                case "2"://已办结（审批通过）
                case "-4"://审核不通过
                    strWhere = " and c.wname not in ('补正补齐(地图审核)','不予受理(地图审核)','地图审核变更审批','地图审核延期审批','地图审核撤销审批','地图审核增量流程','地图内容检定流程')";//包括协审通过
                    break;
            }
            return strWhere;
        }

        /// <summary>
        /// 根据状态返回额外的条件
        /// </summary>
        /// <param name="strStatus">状态位信息</param>
        /// <returns></returns>
        public string GetWhereByStatus(string strStatus)
        {
            //0:已受理;2:已办结;3:已备案;4:协审通过；-1:返回补证;-2:驳回中止;-3:不予受理;-4:审批不通过
            string strWhere = string.Empty;
            switch (strStatus)
            {
                case "0": //已受理
                    strWhere = " or b.status ='1'";
                    break;
                case "2"://已办结
                    //strWhere = " or a.status ='4'";//包括协审通过
                    break;
                case "-1"://返回补证
                    //strWhere = " or c.wname like '%补正补齐%'";
                    break;
                case "-2"://驳回终止
                    strWhere = " or b.status ='-2'";
                    break;
                case "-3"://不予受理
                    //strWhere = " or c.wname like '%不予受理%'";
                    break;
                case "-4"://审核不通过
                    strWhere = "";
                    break;
            }
            return strWhere;
        }

        /// <summary>
        /// 岗位名称
        /// </summary>
        /// <param name="strWiid"></param>
        /// <returns></returns>
        public string GetStep(string strWiid)
        {
            string strSql = string.Format("select step from st_work_item where wiid='{0}'", strWiid);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取岗位信息
        /// </summary>
        /// <param name="strWiid"></param>
        /// <returns></returns>
        public DataTable GetStepDetail(string strWiid)
        {
            string strSql = string.Format("select stepgroup,stepgroupctlid,stepctlid from st_work_item where wiid='{0}'", strWiid);
            DataTable dtReturn = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtReturn);
            return dtReturn;
        }



        /// <summary>
        /// 获取stepGroup
        /// </summary>
        /// <param name="strWiid"></param>
        /// <returns></returns>
        public string GetStepGroup(string strWiid)
        {
            string strSql = string.Format("select stepgroup from st_work_item where wiid='{0}'", strWiid);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 判断是否有终止的权限
        /// </summary>
        /// <param name="strStepName"></param>
        /// <returns></returns>
        public string IsHaveStopRight(string strStepName)
        {
            string strSql = string.Format("select IS_STOP from ST_GROUP where GROUP_NAME='{0}'", strStepName);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取是否有回退信息
        /// </summary>
        /// <param name="striid"></param>
        /// <returns></returns>
        public string GetBackInfo(string striid)
        {
            string strSql = string.Format(@"SELECT SNAME FROM ST_FLOW WHERE Wid in(SELECT Wid FROM                 ST_INSTANCE WHERE IID='{0}' and isdelete <>1) AND Is_Back=1", striid);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取最大iid(通过此值判断是否能够回退)
        /// </summary>
        /// <param name="strIid"></param>
        /// <param name="strUserId"></param>
        /// <returns></returns>
        public string HaveFlowRecord(string strIid, string strUserId)
        {
            string strSql = string.Format(@"Select max(wiid) From st_work_item_hist Where wiid In (select prev_wiid from st_work_item_stack t Where IID='{0}') ", strIid);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取表单数量
        /// </summary>
        /// <param name="wid"></param>
        /// <param name="step"></param>
        /// <param name="path"></param>
        /// <param name="formid"></param>
        /// <returns></returns>
        public string GetFormTableNum(string wid, string step, string path, string formid)
        {
            string strSql = string.Format(@"select Count(wid) from st_workflow_form_right Where wid='{0}'  And step='{1}'
                                            And (form_path='{2}'  OR FormId='{3}' ) And input_right>1", wid, step, path, formid);

            return SysParams.OAConnection().GetValue(strSql);

        }

        /// <summary>
        /// 获取文件扩展名
        /// </summary>
        /// <param name="aid"></param>
        /// <returns></returns>
        public string GetAttachementExtName(string aid)
        {
            string strSql = string.Format(@"select ext_name from st_attachment where aid='{0}'", aid);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取wid
        /// </summary>
        /// <param name="whereValue">扩展条件</param>
        /// <returns></returns>
        public string GetWidFromStinstance(string whereValue)
        {
            string strSql = string.Format(@"select WID from st_instance Where 1=1 {0} ", whereValue);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 判断批量提交件信息中是否存在不同流程名和岗位名
        /// </summary>
        /// <param name="strWIID">业务环节编号</param>
        /// <returns></returns>
        public string IsHaveSameSerialNameAndStep(string strWIID)
        {
            string strSql = string.Format("select count(distinct wid) from sv_work_list_for_worklist_node where wiid in ({0})", strWIID);
            string strCount = SysParams.OAConnection().GetValue(strSql);

            string strReturn = "";
            if (strCount != "1")
            {
                strReturn = "该批次中存在不同的流程，系统不允许提交";
            }
            else
            {
                strSql = string.Format("select count(distinct 所在岗位) from sv_work_list_for_worklist_node where wiid in ({0})", strWIID);
                strCount = SysParams.OAConnection().GetValue(strSql);

                if (strCount != "1")
                {
                    strReturn = "该批次中存在统一流程的不同岗位，系统不允许提交";
                }
            }

            return strReturn;
        }

        /// <summary>
        /// 回退是设置交换位
        /// </summary>
        /// <param name="flag">交换为标志</param>
        /// <param name="remark">原因</param>
        /// <param name="iid">办件编号</param>
        public void UpdateChangeFlag(string flag, string remark, string iid)
        {
            string strSql = string.Format(@"update xt_instance_ext set CAN_SYNC='{0}' ,stop_remark ='{1}' where iid='{2}'", flag, remark, iid);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 更新xt_instance_ext数据
        /// </summary>
        /// <param name="strFlagFieldName">状态列名</param>
        /// <param name="strFlagFieldValue">状态列值</param>
        /// <param name="strContentFieldName">备注字段名</param>
        /// <param name="strContentFieldValue">备注字段值</param>
        /// <param name="strIID">办件编号</param>
        public void UpdateXtInstanceFlag(string strFlagFieldName, string strFlagFieldValue, string strContentFieldName, string strContentFieldValue, string strIID)
        {
            List<string> lstFiledNames = new List<string>();
            List<string> lstFieldValues = new List<string>();

            lstFiledNames.Add(strFlagFieldName);
            lstFiledNames.Add(strContentFieldName);

            lstFieldValues.Add(strFlagFieldValue);
            lstFieldValues.Add(strContentFieldValue);
            OracleSystemOperation osoTemp = new OracleSystemOperation();
            string strWhere = string.Format(" and iid='{0}'", strIID);
            osoTemp.UpdateData(lstFiledNames, lstFieldValues, "xt_instance_ext", strWhere, "update");
        }

        /// <summary>
        /// 更新字段的值
        /// </summary>
        /// <param name="strFieldValue">更新字段值</param>
        /// <param name="strFieldName">更新的字段名</param>
        /// <param name="iid">办件流水号</param>
        public void UpdateFieldValue(string strFieldValue, string strFieldName, string iid, ref IDataAccess dataAccess)
        {
            string strSql = string.Format(@"update xt_submit_info set {0}='{1}' where iid='{2}'", strFieldName, strFieldValue, iid);
            dataAccess.RunSql(strSql);
        }

        /// <summary>
        /// 设置关联字段标识
        /// </summary>
        /// <param name="strFieldValue">更新字段值</param>
        /// <param name="strFieldName">更新的字段名</param>
        /// <param name="iid">办件流水号</param>
        /// <param name="dataAccess"></param>
        public void UpdateGuanLianFieldValue(string strFieldValue, string strFieldName, string iid, ref IDataAccess dataAccess)
        {
            string strSql = string.Format(@"update xt_submit_info set {0}='{1}' where iid in (select 关联id from xt_instance_ext a where a.iid='{2}' and IS_NW='0')", strFieldName, strFieldValue, iid);
            dataAccess.RunSql(strSql);
        }

        /// <summary>
        /// 归档时更新标志位
        /// </summary>
        /// <param name="iid">办件编号</param>
        public void UpdateSubmitFlagInArchiver(string iid, ref IDataAccess dataAccess)
        {
            string strSql = string.Format(@"update xt_instance_ext set status='2' where  iid='{0}'", iid);
            dataAccess.RunSql(strSql);

            strSql = string.Format(@"update xt_submit_info set SUBMITFLAG='2' where iid='{0}'", iid);
            dataAccess.RunSql(strSql);
        }

        /// <summary>
        /// 获取流程名称
        /// </summary>
        /// <param name="iid"></param>
        /// <returns></returns>
        public string GetWnameFromStinsance(string iid)
        {
            string strSql = string.Format("Select WNAME From ST_WORKFLOW Where WID IN(select WID from st_instance Where iid='{0}' and isdelete <>1)", iid);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取消息列表
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="wid"></param>
        /// <returns></returns>
        public DataTable GetMsgList(string iid, string wid)
        {
            string strSql = string.Format(@"select u.userid,u.mobile,u.mobilebak,u.user_name from st_user u where u.userid in( select w.userid from st_work_item w where w.step in (select f.ename from st_flow f where 1=1  and f.wid='{0}') and iid={1} )", wid, iid);
            DataTable dtReturn = dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];

            return dtReturn;
        }

        /// <summary>
        /// 获取操作权限
        /// </summary>
        /// <param name="userid">用户id</param>
        /// <returns></returns>
        public DataTable GetRightList(string userid)
        {
            string strSql = string.Format(@"select u.userid,u.mobile,u.mobilebak,u.user_name from st_user u where u.userid in (select delegate_userid from st_delegate p where p.userid ='{0}')", userid);
            DataTable dtReturn = dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];

            return dtReturn;
        }

        /// <summary>
        /// 根据wiid获取iid
        /// </summary>
        /// <param name="wiid">流程实例环节id</param>
        /// <returns></returns>
        public string GetIidFromWiid(string wiid)
        {
            string strSql = string.Format(@"select iid from st_work_item where wiid='{0}'", wiid);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 根据iid获取wiid
        /// </summary>
        /// <param name="strIID"></param>
        /// <returns></returns>
        public string GetWiidFromIid(string strIID)
        {
            string strSql = string.Format(@"select wiid from st_work_item where iid='{0}' and active=1", strIID);

            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 办件终止时更新案件信息标志位
        /// </summary>
        /// <param name="iid">办件编号</param>
        /// <param name="status">终止状态</param>
        /// <param name="strRemark">终止原因</param>
        public void UpdateStatusFlag(string iid, string status, string strRemark)
        {
            IDataAccess dataAccess = SysParams.OAConnection(true);

            try
            {
                string strSql = string.Format("update st_instance t set t.status='{1}' where t.iid='{0}'", iid, status);
                dataAccess.RunSql(strSql);

                strSql = string.Format("update xt_instance_ext t set t.status='{1}',CAN_SYNC='1',stop_remark ='{2}' where t.iid='{0}' ", iid, status, strRemark);
                dataAccess.RunSql(strSql);

                strSql = string.Format(@"update xt_submit_info set SUBMITFLAG='{1}' where iid=(select 关联ID from xt_instance_ext where iid='{0}')", iid, status);
                dataAccess.RunSql(strSql);

                dataAccess.Close(true);
            }
            catch (Exception ex)
            {
                dataAccess.Close(false);
            }
        }

        /// <summary>
        /// 更新通过状态位
        /// </summary>
        /// <param name="iid">办件编号</param>
        /// <param name="status">状态位(2:通过,4:协审通过；-4：不通过)</param>
        public void UpdatePassStatus(string iid, string status)
        {
            IDataAccess dataAccess = SysParams.OAConnection(true);

            try
            {
                string strSql = string.Format("update xt_instance_ext t set t.status='{1}',CAN_SYNC='1' where t.iid='{0}' ", iid, status);
                dataAccess.RunSql(strSql);

                strSql = string.Format(@"update xt_submit_info set SUBMITFLAG='{1}' where iid='{0}'", iid, status);
                dataAccess.RunSql(strSql);

                dataAccess.Close(true);
            }
            catch (Exception ex)
            {
                dataAccess.Close(false);
            }
        }

        /// <summary>
        /// 发送短信息
        /// </summary>
        /// <param name="iid"></param>
        /// <param name="messagetext"></param>
        public void SetMessage(string iid, string messagetext)
        {
            string strSql = string.Format(@"Insert into xt_messagebox(MESSAGETEXT, PHONENO, USERNAME, USERID)
                                            select username|| ',您好，{1}',a.mobile,username,a.userid
                                            from sys_user a
                                            where userid =(select REQUESTER from XT_SUBMIT_INFO
                                            where iid = (select 关联ID from xt_instance_ext where iid = '{0}'))", iid, messagetext);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取流程中的岗位信息
        /// </summary>
        /// <param name="strWname"></param>
        /// <returns></returns>
        public DataTable GetStepInFlows(string strWname)
        {
            string strSql = string.Format("select distinct a.sname from st_step a where wid in (select wid from st_workflow b where b.wname like '%{0}%' and rot=0）", strWname);
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取表单最新版的id
        /// </summary>
        /// <param name="strFname"></param>
        /// <returns></returns>
        public string GetFormIdByName(string strFname)
        {
            var strSql = string.Format("select fid from st_form where fname='{0}' and rot=0", strFname);
            return SysParams.OAConnection().GetValue(strSql);
        }
    }
}
